package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IExceptionStatisService;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * 异常流水报表统计任务
 */
@Service("exceptionStatisService")
public class ExceptionStatisService implements IExceptionStatisService {

    @Override
    public void execute() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作=====");
        DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourcePre);
        Connection conn = null;
        final String recordName = "tb_exception_statis";
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            conn.setAutoCommit(false);

            //定时任务执行时可能会最后一段时间统计不到
            String recordStr = DBUtils.getCurSynRecordNum(conn, recordName);

            //获取当前系统时间
            SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
            String sysDate = df1.format(new Date());// new Date()为获取当前系统时间

            //获取当天凌晨时间
            String weeHours = weeHours(0);


            XxlJobLogger.log("=====开始同步异常数据统计=====");

            //判断最后更新时间和系统当前时间是否是同一天
            boolean flag = dateIs(recordStr,sysDate);
            if(!flag){
                //上一天凌晨
                String upWeeHours = weeHours(-1).substring(0,10) +" 00:00:00";
                //上一天最后时间
                String upRecordTime = weeHours(-1).substring(0,10) +" 23:59:59";
                insertExceptionStation(upWeeHours,upRecordTime,jdbcTemplate);
                insertExceptionGantry(upWeeHours,upRecordTime,jdbcTemplate);
            }
            insertExceptionStation(weeHours,sysDate,jdbcTemplate);
            insertExceptionGantry(weeHours,sysDate,jdbcTemplate);

            XxlJobLogger.log("=====同步异常数据统计结束=====");

            Integer count = DBUtils.modifySynRecordNum(conn, recordName, recordStr, sysDate);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常][普通]更新记录时间异常");
                throw new Exception("[普通]更新记录时间异常");
            }
            XxlJobLogger.log("[普通]更新记录时间为:" + sysDate);


            conn.commit();
            XxlJobLogger.log("=====完成执行业务操作===");


        }catch(Exception e){
            if (conn != null) {
                try {
                    conn.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }


    public void insertExceptionStation(String weeHours,String sysDate,JdbcTemplate jdbcTemplate) throws Exception{

        //插入TB_STATIS_EXCEPTION_STATION表 异常流水总数
        String emExcSql = "MERGE INTO TB_STATIS_EXCEPTION_STATION A USING (" +
                "  select  OPTIME,STATIONNO,LANENO,count(1) as EXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO,a.LANENO" +
                "  from v_exception_statis a " +
                "  where a.OPTIME >= '"+weeHours+"' and a.OPTIME <= '" + sysDate + "' and a.STATIONNO is not null and a.LANENO is not null )b " +
                "group by b.OPTIME,b.STATIONNO,b.LANENO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.LANE_NO=VES.LANENO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.EXCEPTION_NUM=VES.EXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.LANE_NO,A.EXCEPTION_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.LANENO,VES.EXCOUNT,sysdate) ";
        jdbcTemplate.execute(emExcSql);

        //插入TB_STATIS_EXCEPTION_STATION表 未修正异常流水总数
        String emUnExcSql = "MERGE INTO TB_STATIS_EXCEPTION_STATION A USING (" +
                "  select  OPTIME,STATIONNO,LANENO,count(1) as UNEXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO,a.LANENO" +
                "  from ( " +
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME from binlist_err "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME from etcbinlist_err "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME from boutlist_err "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME from etcboutlist_err)a"+
                "  where a.OPTIME >='" + weeHours + "' and a.OPTIME <= '" + sysDate + "' and a.STATIONNO is not null and a.LANENO is not null )b " +
                "group by b.OPTIME,b.STATIONNO,b.LANENO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.LANE_NO=VES.LANENO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.NODEAL_NUM=VES.UNEXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.LANE_NO,A.EXCEPTION_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.LANENO,VES.UNEXCOUNT,sysdate) ";
        jdbcTemplate.execute(emUnExcSql);


        //插入TB_STATIS_EXCEPTION_STATION表 作废异常流水总数
        String emCancellExcSql = "MERGE INTO TB_STATIS_EXCEPTION_STATION A USING (" +
                "  select  OPTIME,STATIONNO,LANENO,count(1) as CancellEXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO,a.LANENO" +
                "  from ( " +
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME,HADDEAL from tb_charge_binlist "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME,HADDEAL from tb_charge_etcbinlist "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME,HADDEAL from tb_charge_etcboutlist "+
                "union all "+
                "select STATIONNO,to_char(LANENO) as LANENO,to_char(OPTIME,'yyyy-mm-dd hh24:mi:ss') as OPTIME,HADDEAL from etcboutlist_err)a"+
                "  where a.OPTIME >= '" + weeHours + "' and a.OPTIME <= '" + sysDate + "' and a.HADDEAL = 4 and a.STATIONNO is not null and a.LANENO is not null )b " +
                "group by b.OPTIME,b.STATIONNO,b.LANENO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.LANE_NO=VES.LANENO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.INVALID_NUM=VES.CancellEXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.LANE_NO,A.INVALID_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.LANENO,VES.CancellEXCOUNT,sysdate) ";
        jdbcTemplate.execute(emCancellExcSql);

    }


    public void insertExceptionGantry(String weeHours,String sysDate,JdbcTemplate jdbcTemplate) throws Exception{

        //插入TB_STATIS_EXCEPTION_GANTRY表 异常流水总数
        String ganExcSql = "MERGE INTO TB_STATIS_EXCEPTION_GANTRY A USING (" +
                "  select  OPTIME,STATIONNO,count(1) as EXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO" +
                "  from v_exception_statis_gra a " +
                "  where a.OPTIME >= '" + weeHours + "' and a.OPTIME <= '" + sysDate + "' and a.STATIONNO is not null )b " +
                "group by b.OPTIME,b.STATIONNO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.EXCEPTION_NUM=VES.EXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.EXCEPTION_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.EXCOUNT,sysdate) ";
        jdbcTemplate.execute(ganExcSql);


        //插入TB_STATIS_EXCEPTION_GANTRY表 未修正异常流水总数
        String ganUnExcSql = "MERGE INTO TB_STATIS_EXCEPTION_GANTRY A USING (" +
                "  select  OPTIME,STATIONNO,count(1) as UNEXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO" +
                "  from ( " +
                "select (select STATION_ID from tb_charge_grantry_station where GRANTRY_ID = tme.GANTRYID) as STATIONNO ,replace(tme.TRANSTIME,'T',' ') as OPTIME from grantry_trans_main_err tme) a"+
                "  where a.OPTIME >= '" + weeHours + "' and a.OPTIME <= '" + sysDate + "' and a.STATIONNO is not null )b " +
                "group by b.OPTIME,b.STATIONNO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.NODEAL_NUM=VES.UNEXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.NODEAL_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.UNEXCOUNT,sysdate)";
        jdbcTemplate.execute(ganUnExcSql);


        //插入TB_STATIS_EXCEPTION_GANTRY表 作废异常流水总数
        String ganCancellExcSql = "MERGE INTO TB_STATIS_EXCEPTION_GANTRY A USING (" +
                "  select  OPTIME,STATIONNO,count(1) as CancellEXCOUNT from (select substr(a.OPTIME,0,10) as OPTIME,a.STATIONNO" +
                "  from ( " +
                "select (select STATION_ID from tb_charge_grantry_station where GRANTRY_ID = tme.GANTRYID) as STATIONNO ,replace(tme.TRANSTIME,'T',' ') as OPTIME,HADDEAL from tb_charge_grantry_trans_main tme) a"+
                "  where a.OPTIME >= '" + weeHours + "' and a.OPTIME <= '" + sysDate + "' and a.HADDEAL = 4 and a.STATIONNO is not null )b " +
                "group by b.OPTIME,b.STATIONNO"+
                ") VES " +
                "ON (A.station_id=VES.STATIONNO and A.TRADE_DATE = substr('"+sysDate+"',0,10)) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.INVALID_NUM=VES.CancellEXCOUNT,A.UPDATE_TIME= sysdate "+
                "WHEN NOT MATCHED THEN " +
                "  INSERT(A.TRADE_DATE,A.STATION_ID,A.INVALID_NUM,A.INSERT_TIME) " +
                "  VALUES(VES.OPTIME,VES.STATIONNO,VES.CancellEXCOUNT,sysdate) ";
        jdbcTemplate.execute(ganCancellExcSql);

    }



    public String weeHours(Integer day){
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        if(day == -1) {
            calendar.add(Calendar.DAY_OF_MONTH,day); //当前时间减去一天，即一天前的时间
        }else if(day == 0) {
            calendar.set(Calendar.HOUR_OF_DAY, day);
            calendar.set(Calendar.MINUTE, day);
            calendar.set(Calendar.SECOND, day);
        }
        Date zero = calendar.getTime();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String sysDate = df.format(zero);// new Date()为获取当前系统时间
        return sysDate;
    }

    public Boolean dateIs(String recordStr,String sysDate){
        //获取当前系统时间
        SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd");
        String recordTime= recordStr.substring(0,10);
        String sysTime= sysDate.substring(0,10);
        Boolean flag = sysTime.equals(recordTime);
        return flag;
    }
}

